package com.useeinfo.oa.modules.security.dao;

import com.useeinfo.framework.extend.dao.CrudDao;
import com.useeinfo.framework.sugar.data.QueryParam;
import com.useeinfo.framework.sugar.data.QueryUtils;
import com.useeinfo.framework.sugar.data.SelectVO;
import com.useeinfo.framework.sugar.tools.StringConverters;
import com.useeinfo.oa.modules.security.entity.SystemUser;
import net.sf.json.JSONArray;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import java.util.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

/**
 * Author: 居泽平
 */
@Repository("systemUserDaoWeb")
public class SystemUserDao implements CrudDao<SystemUser> {

    private final static Logger logger = LoggerFactory.getLogger(SystemUserDao.class);

    @PersistenceContext
    private EntityManager entityManager;

    public SystemUser login(String username, String password) {
        List<SystemUser> customers = entityManager.createNamedQuery("SystemUser.login", SystemUser.class)
                .setParameter("userAccount", username)
                .setParameter("userPassword", password)
                .getResultList();
        if (CollectionUtils.isEmpty(customers)) {
            return null;
        } else {
            return customers.get(0);
        }
    }

    public List<SystemUser> findByRoleId(Long roleId) {
        List<SystemUser> systemUserList = this.entityManager.createQuery("select su from SystemUser su where su.systemRole.id = :roleId", SystemUser.class).setParameter("roleId", roleId).getResultList();
        if (CollectionUtils.isEmpty(systemUserList)) {
            return null;
        }
        return systemUserList;
    }

    //根据企业Id,区域查询人员list
    public List<Object> getSystemUserByArea(String areaId,String enterpriseId,String authorityName,int no,int size,int type){
        String search;
        if(type==0){
            search="er.id";
        }else{
            search="count(*)";
        }
        String sql="select "+search+" FROM system_user er WHERE er.delete_status= false and er.id in (select ep.system_user_id FROM enterprise_personnel_system_user ep WHERE  ep.delete_status= false and ep.enterprise_id="+enterpriseId+")" ;

        if(areaId!=null){
            sql=sql+ "and er.id in (select pa.system_user_id FROM system_user_2_area pa WHERE pa.area_id="+areaId+") " ;
        }
        sql=sql+ "AND er.id in (select ue.user_id FROM user_role_enterprise ue WHERE  ue.delete_status= false and ue.role_id in " +
                "(select su.role_id FROM system_role_2_authority su WHERE su.authority_id in " +
                "(Select sya.id FROM system_authority sya WHERE sya.authority_name='"+authorityName+"')))";

        if(type==0){
            sql=sql+"limit "+(no-1)*size+"," +size;
        }
        logger.info("拼接完成的sql为：{}",sql);

        List<Object>objects=entityManager.createNativeQuery(sql).getResultList();

        if(CollectionUtils.isNotEmpty(objects)){
            return objects;
        }else{
            return null;
        }

    }

    //根据部门id查询人员List
    public List<SelectVO> getSystemUserByOrgan(String organizationId) {
        String sql = "SELECT * FROM SYSTEM_USER p WHERE  p.delete_status= false and p.id in (select op.system_user_id FROM system_user_organization op WHERE  op.delete_status= false and op.organization_id="+organizationId+" ) ";
        logger.info("拼接完成的sql语句为：{}",sql);
        List<Object>objects=entityManager.createNativeQuery(sql).getResultList();
        List<SelectVO> selectVOS=new ArrayList<>();
        if (CollectionUtils.isNotEmpty(objects)) {
            for (Object object : objects) {
                JSONArray jsonArray1 = JSONArray.fromObject(object);
                SelectVO selectVO=new SelectVO();
                selectVO.setValue(jsonArray1.get(0).toString());
                selectVO.setText(jsonArray1.get(11).toString());
                selectVOS.add(selectVO);
            }
        }
        return selectVOS;
    }

    //根据岗位查询人员list
    public List<Long> getUserIdsByPosition(String positionId,String organizationId,String enterpriseId) {
        String sql = "SELECT p.id FROM SYSTEM_USER p WHERE  p.delete_status= false and p.id in (select sp.system_user_id FROM system_user_2_position sp WHERE sp.position_id="+positionId+" ) " +
                "and p.id in (select suo.system_user_id from system_user_organization suo where  suo.delete_status= false and suo.organization_id ="+organizationId+") " ;
        if(StringUtils.isNotEmpty(organizationId)){
            sql=sql+"and p.id in (select ep.system_user_id from enterprise_personnel_system_user ep where  ep.delete_status= false and ep.enterprise_id ="+enterpriseId+")";
        }
        logger.info("拼接完成的sql语句为：{}",sql);
        List<Object>thisIds=entityManager.createNativeQuery(sql).getResultList();
        List<Long>ids=new ArrayList<>();
        if(CollectionUtils.isNotEmpty(thisIds)){
            for(Object o:thisIds){
                ids.add(StringConverters.null2Long(o));
            }
        }
        return ids;
    }


    // ******************************************************************************
    // ******************************* 代码自动生成开始 *******************************
    // ******************************************************************************

    public Map<String, Object> getSearchCondition(Map<String, String> queryHash) {

        Map<String, Object> conditionHash = new HashMap<>();

        conditionHash.put("s.deleteStatus = ?{paramIndex} ", false);

        if (queryHash == null || queryHash.size() == 0) {
            return conditionHash;
        }

        String searchCode = queryHash.get("searchCode");
        if (!StringUtils.isEmpty(searchCode)) {
            conditionHash.put("(s.userName like ?{paramIndex} or s.userAccount like ?{paramIndex}) ", "%" + searchCode + "%");
        }

        String userName = queryHash.get("userName");
        if (!StringUtils.isEmpty(userName)) {
            conditionHash.put("s.userName like ?{paramIndex} ", "%" + userName + "%");
        }

        String workNo = queryHash.get("workNo");
        if (!StringUtils.isEmpty(workNo)) {
            conditionHash.put("s.workNo like ?{paramIndex} ", "%" + workNo + "%");
        }

        String organizationName = queryHash.get("organizationName");
        if (!StringUtils.isEmpty(organizationName)) {
            conditionHash.put("and EXISTS(SELECT uo FROM s.systemUserOrganizationSet uo WHERE uo.organization.organizationName like (?{paramIndex})) ", "%" + organizationName + "%");
        }

        String positionName = queryHash.get("positionName");
        if (!StringUtils.isEmpty(positionName)) {
            conditionHash.put("and EXISTS(SELECT p FROM s.positionSet p WHERE p.name like (?{paramIndex})) ", "%" + positionName + "%");
        }

        String positionId = queryHash.get("positionId");
        if (!StringUtils.isEmpty(positionId)) {
            conditionHash.put("and EXISTS(SELECT p FROM s.positionSet p WHERE p.id = (?{paramIndex})) ", StringConverters.null2Long(positionId) );
        }
        String userAccount = queryHash.get("userAccount");
        if (!StringUtils.isEmpty(userAccount)) {
            conditionHash.put("s.userAccount = ?{paramIndex} ", userAccount);
        }

        String uniqueid = queryHash.get("uniqueid");
        if (!StringUtils.isEmpty(uniqueid)) {
            conditionHash.put("s.uniqueid = ?{paramIndex} ", uniqueid);
        }

        String password = queryHash.get("password");
        if (!StringUtils.isEmpty(password)) {
            conditionHash.put("s.userPassword = ?{paramIndex} ", password);
        }

        String id = queryHash.get("id");
        if (!StringUtils.isEmpty(id)) {
            conditionHash.put("s.id = ?{paramIndex} ", StringConverters.ToLong(id));
        }

        String operationRole = queryHash.get("operationRole");
        if (!StringUtils.isEmpty(operationRole)) {
            conditionHash.put("s.operationRole = ?{paramIndex} ", StringConverters.null2String(operationRole));
        }

        String roleName = queryHash.get("roleName");
        if (!StringUtils.isEmpty(roleName)) {
            conditionHash.put("s.systemRole.roleName = ?{paramIndex} ", roleName);
        }

        String wxUserId = queryHash.get("wxUserId");
        if (!StringUtils.isEmpty(wxUserId)) {
            conditionHash.put("s.wxUserId = ?{paramIndex} ", wxUserId);
        }

        String magicLike = queryHash.get("magicLike");
        if (!StringUtils.isEmpty(magicLike)) {
            conditionHash.put("userName like ?{paramIndex} ", "%" + magicLike + "%");
        }
        String magicId = queryHash.get("magicId");
        if (StringUtils.isNotBlank(magicId)) {
            conditionHash.put("id != ?{paramIndex} ", StringConverters.ToLong(magicId));
        }
        String operationRoles = queryHash.get("operationRoles");
        if (StringUtils.isNotBlank(operationRoles)) {
            String[] ops = operationRoles.split(",");
            StringBuilder sql = new StringBuilder("and operationRole in(");
            for (String op : ops) {
                sql.append("'").append(op).append("',");
            }
            sql.deleteCharAt(sql.length() - 1);
            sql.append(")");
            conditionHash.put(sql + " and 1 = ?{paramIndex} ", 1);
        }
        String enterpriseIds = queryHash.get("enterpriseIds");
        if (StringUtils.isNotBlank(enterpriseIds)) {
            Set<Long> enterpriseSet = new HashSet<>();
            String[] enters = enterpriseIds.split(",");
            for (String enter : enters) {
                enterpriseSet.add(StringConverters.null2Long(enter));
            }
            conditionHash.put("and EXISTS(SELECT enterprise FROM s.enterpriseSet enterprise WHERE enterprise.id in (?{paramIndex})) ", enterpriseSet);
        }
        String organizationIds = queryHash.get("organizationIds");
        if (StringUtils.isNotBlank(organizationIds)) {
            Set<Long> organizationSet = new HashSet<>();
            String[] organizas = organizationIds.split(",");
            for (String organiza : organizas) {
                organizationSet.add(StringConverters.null2Long(organiza));
            }
            conditionHash.put("and EXISTS(SELECT so FROM s.systemUserOrganizationSet so WHERE so.organization.id in (?{paramIndex})) ", organizationSet);
        }
        String positions = queryHash.get("positions");
        if (StringUtils.isNotBlank(positions)) {
            Set<Long> positionSet = new HashSet<>();
            String[] posits = positions.split(",");
            for (String posit : posits) {
                positionSet.add(StringConverters.null2Long(posit));
            }
            conditionHash.put("and EXISTS(SELECT p FROM s.positionSet p WHERE p.id in (?{paramIndex})) ", positionSet);
        }
        return conditionHash;
    }

    @Override
    public Long totalRecord(Map<String, String> queryHash) {

        Map<String, Object> conditions = getSearchCondition(queryHash);
        TypedQuery<Long> typedQuery = QueryUtils.getTypedQueryByCondition("select count(s) from SystemUser s ", conditions, entityManager, Long.class);
        return typedQuery.getSingleResult();
    }

    @Override
    public List<SystemUser> findList(QueryParam queryParam) {

        String sqlInfo = queryParam.joinJPQL("select s from SystemUser s ", "order by s.id desc ");

        Map<String, Object> conditions = getSearchCondition(queryParam.getSqlMap());
        TypedQuery<SystemUser> typedQuery = QueryUtils.getTypedQueryByCondition(sqlInfo, conditions, entityManager, SystemUser.class);

        return queryParam.findPageList(typedQuery);
    }

    @Override
    public SystemUser findModel(Long userId) {
        return entityManager.find(SystemUser.class, userId);
    }

    @Override
    public Integer add(SystemUser systemUser) {
        systemUser.setUniqueid(UUID.randomUUID().toString());
        entityManager.persist(systemUser);
        logger.info("SystemUserDaoImpl添加systemUser成功！");
        return 1;
    }

    @Override
    public Integer update(SystemUser systemUser) {
		SystemUser existSystemUser = entityManager.find(SystemUser.class, systemUser.getId());
		existSystemUser.setUserAccount(systemUser.getUserAccount());
		existSystemUser.setMobile(systemUser.getMobile());
		existSystemUser.setEmail(systemUser.getEmail());
		existSystemUser.setUserPassword(systemUser.getUserPassword());
		existSystemUser.setUserName(systemUser.getUserName());
		existSystemUser.setWorkNo(systemUser.getWorkNo());
		existSystemUser.setSex(systemUser.getSex());
		existSystemUser.setUniqueid(systemUser.getUniqueid());
		existSystemUser.setEnabled(systemUser.isEnabled());
		existSystemUser.setOperationRole(systemUser.getOperationRole());
        return 1;
    }

    public void logicRemove(Long userId) {
        SystemUser existSystemUser = entityManager.find(SystemUser.class, userId);
        existSystemUser.setDeleteStatus(true);
    }

    @Override
    public Integer delete(Long userId) {
        SystemUser existSystemUser = entityManager.find(SystemUser.class, userId);
        entityManager.remove(existSystemUser);
        return 1;
    }

    @Override
    public Integer deleteLogic(SystemUser model) {
        SystemUser systemUser = entityManager.find(SystemUser.class, model.getId());
        systemUser.setDeleteStatus(true);
        return 1;
    }


    // ******************************************************************************
    // ******************************* 代码自动生成结束 *******************************
    // ******************************************************************************

    public List<SystemUser> findUser(String jpql, Map<String, Object> params) {

        Query query = entityManager.createQuery(jpql);
        if (params != null) {
            for (String key : params.keySet()) {
                query.setParameter(key, params.get(key));
            }
        }
        return query.getResultList();
    }

    public List<SystemUser> findObjects(String jpql, Map<String, Object> params) {

        Query query = entityManager.createQuery(jpql);
        if (params != null) {
            for (String key : params.keySet()) {
                query.setParameter(key, params.get(key));
            }
        }
        return query.getResultList();
    }

    public List<Object> findListByNative(String sql) {

        Query query = entityManager.createNativeQuery(sql);
        return query.getResultList();
    }

    /**
     * 删除用户所有的公司
     *
     * @param userId
     * @return
     */
    public synchronized Integer deleteEnterpriseByUserId(Long userId) {
        String sql = "DELETE FROM system_user_2_enterprise WHERE user_id=" + userId;
        Query query = entityManager.createNativeQuery(sql);
        try {
            return query.executeUpdate();
        } catch (Exception ex) {
            return 0;
        }
    }

    /**
     * 获取用户所有的公司
     *
     * @param userId
     * @return
     */
    public List getEnterpriseByUserId(Long userId) {
        String sql = "SELECT enterprise_id FROM `system_user_2_enterprise` WHERE user_id=" + userId;
        return entityManager.createNativeQuery(sql).getResultList();
    }
    public List<SystemUser> hqlExcute(String hql,QueryParam queryParam) {
        return queryParam.findPageList(entityManager.createQuery(hql,SystemUser.class));
    }
    public List<SystemUser> hqlExcute(String hql) {
        return entityManager.createQuery(hql,SystemUser.class).getResultList();
    }

    /**
     * 通过企业id 获取 可选参与人员列表
     * @param pageNow
     * @param pageSize
     * @param enterpriseId 企业id
     * @param userName 模糊查询 查找人名字
     * @return List<Object>
     */
    public List<Object> findParticipationUserByEnterprise(Integer pageNow, Integer pageSize, Long enterpriseId, String userName) {
        String sql = "SELECT suu.*, enter.enterprise_name, post.`name` FROM system_user suu  INNER JOIN system_user_2_enterprise sue ON suu.id = sue.user_id AND sue.enterprise_id = '5' INNER JOIN user_role_enterprise urr ON urr.user_id = suu.id  INNER JOIN system_role_2_authority sr2a ON sr2a.role_id = urr.role_id INNER JOIN system_authority saaa ON saaa.id = sr2a.authority_id INNER JOIN enterprise enter ON sue.enterprise_id = enter.id INNER JOIN system_user_2_position supp  ON supp.system_user_id = suu.id  INNER JOIN position post ON post.id = supp.position_id WHERE saaa.authority_name = 'ROLE_ENTERPRISE_TASK_ORDER_TAKING' ";


        return entityManager.createNativeQuery(sql).getResultList();
    }
}